USE [TBbilling]
GO
/****** Object:  StoredProcedure [dbo].[Receipt]    Script Date: 02/06/2015 15:43:14 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- exec receipt '12/12/2014','1/1/2015'
-- =============================================
CREATE PROCEDURE [dbo].[Receipt]   @FromDT datetime, @ToDT DateTime
	
AS
BEGIN
Set nocount on 

Declare
	@option  char(3)
	
	


set	@option  = 'ALL'


select distinct  b.Bill_No ' Patient Account Number'
  ,'' 'Payment Payer Name'
  , isnull(trs.Description,'') 'Item Description'
   , isnull(trs.amount,0)  'Payment Amount'
 , trs.transaction_date  'Payment date'
  , isnull((b.total_charges1+b.total_charges2+b.total_charges3)-(Payment_Adjustment0+Payment_Adjustment1+Payment_Adjustment2+Payment_Adjustment3),0) 'Account Balance'
   , Case  
 when trs.description  = 'ADMIN ADJ - CLIN' then 
       (select distinct Action_code from Angus.dbo.ar_Action_Codes
 		where (action_Type = 'A'		
 		and description = trs.Description
		and ar_Action_Codes.financial_classID = 2))
		  when trs.description  = 'ADMIN ADJ - HOSP' then
       (select distinct Action_code from Angus.dbo.ar_Action_Codes
 		where (action_Type = 'A'		
 		and description = trs.Description
		and ar_Action_Codes.financial_classID = 2))
		else   (select distinct Action_code from Angus.dbo.ar_Action_Codes
 		where (action_Type = trs.Action_Type		
 		and description = trs.Description
		))end 'Item Mnemonic'
 
  from Angus.dbo.ar_bills b (nolock)
   inner join Angus.dbo.shr_Financial_classes fc (nolock) on b.current_financial_classid=fc.financial_classid and fc.companyID=2
   inner join Angus.dbo.b_charges bc (nolock) on b.bill_no=bc.bill_no and b.episodeid=bc.episodeid
   inner join Angus.dbo.shr_charge_codes c (nolock) on c.Charge_CodeID=bc.Charge_CodeID
   inner join Angus.dbo.shr_UB92_Revenue_Codes rc on c.Revenue_CodeId=rc.Revenue_Code_Id
   inner join Angus.dbo.shr_episodes e (nolock) on b.episodeid=e.episodeid
   inner join Angus.dbo.glb_People_insurances gpi (nolock) on e.Patient_InsuranceID1=gpi.People_InsuranceID
   inner Join Angus.dbo.glb_Insurance_Companies ic With (NoLock) on gpi.Insurance_CompanyID=ic.Insurance_CompanyID
   inner Join Angus.dbo.shr_Insurance_Company_Fin_Classes icf (nolock) on gpi.Insurance_CompanyID=icf.Insurance_CompanyID
   inner join Angus.dbo.shr_Financial_classes afc (nolock) on icf.financial_classid=afc.financial_classid and afc.companyID=2
   inner join Angus.dbo.shr_mpi mpi (nolock) on mpi.mpiid=e.mpiid
   LEFT JOIN Angus.dbo.shr_MPI_Contacts smc WITH (NOLOCK) ON smc.MPIID = mpi.MPIID
   inner join Angus.dbo.glb_people p (nolock) on p.peopleid=mpi.peopleid
   left join Angus.dbo.glb_Employers gemployer1 (nolock) on gemployer1.EmployerID=p.EmployerID
   inner join Angus.dbo.shr_patient_classes pc (nolock) on e.patient_classid=pc.patient_classid and pc.companyid=2
   inner join Angus.dbo.shr_Guarantors g (nolock) on g.guarantorid=b.guarantorid
   inner join Angus.dbo.glb_people gp (nolock) on gp.peopleid=g.peopleid
   left join Angus.dbo.glb_Employers gemployer (nolock) on gemployer.EmployerID=gp.EmployerID
   left join Angus.dbo.shr_transactions trs (nolock) on ( b.billid= trs.billid and trs.Action_Type='P')
inner join TBbilling.dbo.patientTransfer TBInfo on (b.BillID = TBInfo.billid
														and b.EpisodeID = TBInfo.episodeID)
  WHERE --Discharge_Datetime between @FromDT and @ToDT
   --and 
   trs.transaction_date between @FromDT and @ToDT
  and    trs.amount is not null
   and fc.Financial_Class in ('TB','TA','TR','TI','TM')
   and (1=CASE WHEN @option='Z' AND (b.total_charges1+b.total_charges2+b.total_charges3)-(Payment_Adjustment0+Payment_Adjustment1+Payment_Adjustment2+Payment_Adjustment3)=0  THEN 1
   WHEN @option<>'Z' AND (b.total_charges1+b.total_charges2+b.total_charges3)-(Payment_Adjustment0+Payment_Adjustment1+Payment_Adjustment2+Payment_Adjustment3)>0  THEN 1
   ELSE 0
   END)
   and CHARINDEX(ISNULL(b.STATUS,''),CASE WHEN (@option='ALL' OR @option='Z') --All Bills    
            THEN ''
            ELSE 'B'    --Bad Debt Bills    
          END)
         =CASE WHEN @option='B'
            THEN 1
            ELSE 0
         END
order by b.Bill_No
 
 
set nocount off
End
GO
